import pandas as pd
import numpy as np

# 读取Excel文件
file_path = "餐饮连锁数据.xlsx"

# 读取三个工作表
store_df = pd.read_excel(file_path, sheet_name="门店信息")
dishes_df = pd.read_excel(file_path, sheet_name="菜品信息")
sales_df = pd.read_excel(file_path, sheet_name="销售记录")

print("原始数据形状：")
print(f"门店信息: {store_df.shape}")
print(f"菜品信息: {dishes_df.shape}")
print(f"销售记录: {sales_df.shape}")

# 定义异常数据收集器
abnormal_data = {}


# 1. 清洗门店信息表
def clean_store_data(df):
    """清洗门店信息表"""
    abnormalities = {}

    # 检查缺失值
    missing_store_ids = df[df['门店ID'].isna()]
    if not missing_store_ids.empty:
        abnormalities['缺失门店ID'] = missing_store_ids

    # 检查重复门店ID
    duplicate_stores = df[df.duplicated('门店ID', keep=False)]
    if not duplicate_stores.empty:
        abnormalities['重复门店ID'] = duplicate_stores

    # 检查门店ID格式
    invalid_format_stores = df[~df['门店ID'].str.startswith('R', na=False)]
    if not invalid_format_stores.empty:
        abnormalities['格式错误门店ID'] = invalid_format_stores

    return abnormalities


# 2. 清洗菜品信息表
def clean_dishes_data(df):
    """清洗菜品信息表"""
    abnormalities = {}

    # 检查缺失值
    missing_dish_id = df[df['菜品ID'].isna()]
    missing_dish_name = df[df['菜品名称'].isna()]
    missing_price = df[df['单价(元)'].isna()]

    if not missing_dish_id.empty:
        abnormalities['缺失菜品ID'] = missing_dish_id
    if not missing_dish_name.empty:
        abnormalities['缺失菜品名称'] = missing_dish_name
    if not missing_price.empty:
        abnormalities['缺失单价'] = missing_price

    # 检查异常数值
    # 单价异常（负数、0或过大值）
    abnormal_price = df[(df['单价(元)'] <= 0) | (df['单价(元)'] > 500)]
    if not abnormal_price.empty:
        abnormalities['异常单价'] = abnormal_price

    # 评分异常（正常范围0-5，但数据中有超出范围的）
    rating_columns = ['口味评分', '分量评分', '颜值评分', '上菜速度评分']
    for col in rating_columns:
        if col in df.columns:
            # 先处理NaN值
            df_temp = df.dropna(subset=[col])
            abnormal_ratings = df_temp[(df_temp[col] < 0) | (df_temp[col] > 5)]
            if not abnormal_ratings.empty:
                abnormalities[f'异常{col}'] = abnormal_ratings

    # 检查重复菜品ID
    duplicate_dishes = df[df.duplicated('菜品ID', keep=False)]
    if not duplicate_dishes.empty:
        abnormalities['重复菜品ID'] = duplicate_dishes

    # 检查菜品类别是否有效
    valid_categories = ['主食', '小吃', '甜品', '热菜', '饮品', '凉菜']
    invalid_categories = df[~df['菜品类别'].isin(valid_categories)]
    if not invalid_categories.empty:
        abnormalities['无效菜品类别'] = invalid_categories

    return abnormalities


# 3. 清洗销售记录表
def clean_sales_data(df, store_df, dishes_df):
    """清洗销售记录表"""
    abnormalities = {}

    # 检查缺失值
    missing_store_id = df[df['门店ID'].isna()]
    missing_dish_id = df[df['菜品ID'].isna()]
    missing_date = df[df['日期'].isna()]
    missing_quantity = df[df['销售数量'].isna()]

    if not missing_store_id.empty:
        abnormalities['缺失门店ID'] = missing_store_id
    if not missing_dish_id.empty:
        abnormalities['缺失菜品ID'] = missing_dish_id
    if not missing_date.empty:
        abnormalities['缺失日期'] = missing_date
    if not missing_quantity.empty:
        abnormalities['缺失销售数量'] = missing_quantity

    # 检查外键关联
    invalid_store_ids = df[~df['门店ID'].isin(store_df['门店ID'])]
    invalid_dish_ids = df[~df['菜品ID'].isin(dishes_df['菜品ID'])]

    if not invalid_store_ids.empty:
        abnormalities['无效门店ID'] = invalid_store_ids
    if not invalid_dish_ids.empty:
        abnormalities['无效菜品ID'] = invalid_dish_ids

    # 检查异常数值
    # 销售数量异常（负数、0或过大值）
    # 先处理NaN值
    df_temp = df.dropna(subset=['销售数量'])
    abnormal_quantity = df_temp[(df_temp['销售数量'] <= 0) | (df_temp['销售数量'] > 200)]
    if not abnormal_quantity.empty:
        abnormalities['异常销售数量'] = abnormal_quantity

    # 成本率异常（正常范围0-1，但数据中有超出范围的）
    if '成本率' in df.columns:
        df_temp_cost = df.dropna(subset=['成本率'])
        abnormal_cost = df_temp_cost[(df_temp_cost['成本率'] < 0) | (df_temp_cost['成本率'] > 1)]
        if not abnormal_cost.empty:
            abnormalities['异常成本率'] = abnormal_cost

    # 检查重复记录
    duplicate_sales = df[df.duplicated(['门店ID', '菜品ID', '日期', '时段'], keep=False)]
    if not duplicate_sales.empty:
        abnormalities['重复销售记录'] = duplicate_sales

    # 检查时段格式
    valid_time_slots = ['06:00-09:00', '09:00-11:00', '11:00-13:00',
                        '13:00-17:00', '17:00-20:00', '20:00-23:00']
    invalid_time_slots = df[~df['时段'].isin(valid_time_slots)]
    if not invalid_time_slots.empty:
        abnormalities['无效时段'] = invalid_time_slots

    return abnormalities


# 执行数据清洗
print("\n开始数据清洗...")

# 清洗门店信息
store_abnormalities = clean_store_data(store_df)
abnormal_data['门店信息'] = store_abnormalities

# 清洗菜品信息
dishes_abnormalities = clean_dishes_data(dishes_df)
abnormal_data['菜品信息'] = dishes_abnormalities

# 清洗销售记录
sales_abnormalities = clean_sales_data(sales_df, store_df, dishes_df)
abnormal_data['销售记录'] = sales_abnormalities

# 输出异常数据统计
print("\n=== 异常数据统计 ===")
total_abnormal_records = 0

for sheet_name, abnormalities in abnormal_data.items():
    print(f"\n{sheet_name}表异常情况：")
    if not abnormalities:
        print("  ✅ 无异常数据")
    else:
        for abnormal_type, abnormal_df in abnormalities.items():
            count = len(abnormal_df)
            total_abnormal_records += count
            print(f"  ❌ {abnormal_type}: {count}条记录")

print(f"\n总计发现 {total_abnormal_records} 条异常记录")

# 显示部分异常数据详情
print("\n=== 异常数据详情示例 ===")
for sheet_name, abnormalities in abnormal_data.items():
    if abnormalities:
        print(f"\n{sheet_name}表异常数据示例：")
        for abnormal_type, abnormal_df in abnormalities.items():
            if len(abnormal_df) > 0:
                print(f"\n{abnormal_type} (前3条):")
                display_cols = abnormal_df.columns.tolist()[:5]  # 只显示前5列
                print(abnormal_df[display_cols].head(3).to_string())


# 修复后的清洗数据函数
def get_cleaned_data(original_df, abnormalities_dict):
    """获取清洗后的数据（移除所有异常记录）"""
    cleaned_df = original_df.copy()

    # 收集所有要删除的索引
    all_indices_to_remove = set()

    for abnormal_type, abnormal_df in abnormalities_dict.items():
        if not abnormal_df.empty:
            # 添加异常记录的索引
            all_indices_to_remove.update(abnormal_df.index)

    # 一次性删除所有异常记录
    if all_indices_to_remove:
        cleaned_df = cleaned_df.drop(index=list(all_indices_to_remove))

    return cleaned_df


# 获取清洗后的数据
cleaned_store_df = get_cleaned_data(store_df, store_abnormalities)
cleaned_dishes_df = get_cleaned_data(dishes_df, dishes_abnormalities)
cleaned_sales_df = get_cleaned_data(sales_df, sales_abnormalities)

print(f"\n清洗后数据形状：")
print(f"门店信息: {cleaned_store_df.shape} (移除了 {len(store_df) - len(cleaned_store_df)} 条记录)")
print(f"菜品信息: {cleaned_dishes_df.shape} (移除了 {len(dishes_df) - len(cleaned_dishes_df)} 条记录)")
print(f"销售记录: {cleaned_sales_df.shape} (移除了 {len(sales_df) - len(cleaned_sales_df)} 条记录)")


# 保存异常数据到Excel文件
def save_abnormal_data_to_excel(abnormal_data, filename="异常数据报告.xlsx"):
    """将异常数据保存到Excel文件"""
    with pd.ExcelWriter(filename, engine='openpyxl') as writer:
        for sheet_name, abnormalities in abnormal_data.items():
            if abnormalities:
                for abnormal_type, abnormal_df in abnormalities.items():
                    if not abnormal_df.empty:
                        # 创建有意义的sheet名称
                        sheet_name_clean = f"{sheet_name}_{abnormal_type}"[:31]  # Excel sheet名称限制31字符
                        # 处理sheet名称中的特殊字符
                        sheet_name_clean = sheet_name_clean.replace(':', '').replace('\\', '').replace('/', '')
                        abnormal_df.to_excel(writer, sheet_name=sheet_name_clean, index=False)

    print(f"\n异常数据报告已保存到: {filename}")


# 保存异常数据
save_abnormal_data_to_excel(abnormal_data)


# 保存清洗后的数据（可选）
def save_cleaned_data(cleaned_store, cleaned_dishes, cleaned_sales, filename="清洗后数据.xlsx"):
    """保存清洗后的数据到Excel文件"""
    with pd.ExcelWriter(filename, engine='openpyxl') as writer:
        cleaned_store.to_excel(writer, sheet_name="门店信息_清洗后", index=False)
        cleaned_dishes.to_excel(writer, sheet_name="菜品信息_清洗后", index=False)
        cleaned_sales.to_excel(writer, sheet_name="销售记录_清洗后", index=False)

    print(f"清洗后数据已保存到: {filename}")


save_cleaned_data(cleaned_store_df, cleaned_dishes_df, cleaned_sales_df)

print("\n数据清洗完成！")

# 显示具体的异常数据示例
print("\n=== 详细异常数据示例 ===")

# 菜品信息表的异常示例
if '菜品信息' in abnormal_data and abnormal_data['菜品信息']:
    print("\n菜品信息表主要异常：")
    for abnormal_type, df in abnormal_data['菜品信息'].items():
        if not df.empty:
            print(f"\n{abnormal_type}:")
            sample = df[['菜品ID', '菜品名称', '菜品类别', '单价(元)']].head(5)
            print(sample.to_string(index=False))

# 销售记录表的异常示例
if '销售记录' in abnormal_data and abnormal_data['销售记录']:
    print("\n销售记录表主要异常：")
    for abnormal_type, df in abnormal_data['销售记录'].items():
        if not df.empty:
            print(f"\n{abnormal_type}:")
            sample_cols = ['门店ID', '菜品ID', '销售数量', '成本率'] if '成本率' in df.columns else ['门店ID', '菜品ID',
                                                                                                     '销售数量']
            sample = df[sample_cols].head(5)
            print(sample.to_string(index=False))